package sv.gob.mh.dgii.dao.rc;

import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.commons.beanutils.DynaBean;
import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Property;
import org.springframework.orm.ObjectRetrievalFailureException;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import sv.gob.mh.dgii.common.Utils;
import sv.gob.mh.dgii.dao.hibernate.HibernateDaoImpl;
import sv.gob.mh.dgii.model.ImSolicitud;
import sv.gob.mh.dgii.model.RcCaractTrib;
import sv.gob.mh.dgii.model.RcDocIdentidad;
import sv.gob.mh.dgii.model.RcDomicilio;
import sv.gob.mh.dgii.model.RcNomComerc;
import sv.gob.mh.dgii.model.RcRuc;

@Repository
@Transactional
public class RcRucDaoImpl extends HibernateDaoImpl implements RcRucDao {

    private final String _QUERY_TARJETA_NIT = "RcRuc.uniqueTarjetaNit";
    private final String _QUERY_TARJETA_NRC = "RcRuc.uniqueTarjetaNrc";
    private final int _RC_MAX_RESULTS = 1500;

    public RcRucDaoImpl() {
        super(RcRuc.class);
    }

    private boolean isValidSet(Set<?> set) {
        return set != null && set.size() > 0;
    }

    public List<DynaBean> findVehiculos(String nit) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("p_nit", nit);
        List lst = executeSqlNamedQuery("RcRuc.findVehiculos", params);
        return (List<DynaBean>) lst;
    }

    public List<DynaBean> findBienesRaices(String nit) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("p_nit", nit);
        List lst = executeSqlNamedQuery("RcRuc.findBienesRaices", params);
        return (List<DynaBean>) lst;
    }

    public List<DynaBean> findCasoxTecnico(String nit, String nit2) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("p_tecnico", nit);
        params.put("p_nit", nit2);
        List lst = executeSqlNamedQuery("RcRuc.findCasoxTecnico", params);
        return (List<DynaBean>) lst;
    }

    public List<DynaBean> findCasos(String nit, String anio) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("p_nit", nit);
        params.put("p_anio", anio);
        List lst = executeSqlNamedQuery("RcRuc.findCasos", params);
        return (List<DynaBean>) lst;
    }

    public List getByTipoContrib(RcRuc rcRuc) {
        String hql = "select distinct a from RcRuc a ";
        // + " left outer join a.rcCaractTribs ct ";

        if (isValidSet(rcRuc.getRcNomComercs())) {
            hql += " left outer join a.rcNomComercs nc ";
        }

        if (isValidSet(rcRuc.getRcDocIdentidads())) {
            hql += " left outer join a.rcDocIdentidads di ";
        }

        if (isValidSet(rcRuc.getRcDomicilios())) {
            hql += " left outer join a.rcDomicilios dom";
        }

        if (isValidSet(rcRuc.getRcCaractTribs())) {
            hql += " left outer join a.rcCaractTribs ct ";
            hql += " where a.nit = ct.rcRuc.nit ";
        } else {
            hql += " where a.nit = a.nit ";
        }

        String nit = rcRuc.getNit();
        if (rcRuc.getNit() != null && rcRuc.getNit().trim().length() > 0) {
            if (nit.indexOf("%") != -1) {
                hql += "and a.nit like '" + rcRuc.getNit().trim() + "'";
            } else {
                hql += "and a.nit = '" + rcRuc.getNit().trim() + "'";
            }
        }

        if (rcRuc.getFnacConst() != null) {
            hql += " and a.fnacConst = ( to_date('"
                    + Utils.getFormattedDate(rcRuc.getFnacConst())
                    + "','dd/mm/yyyy') )";
        }

        // Si esta activo o no?
        if (rcRuc.getBactivo() != null) {
            hql += " and a.bactivo = '" + rcRuc.getBactivo() + "'";
        }

        // validateSpecialCharacters
        if (!Utils.isNullOrEmpty(rcRuc.getTbTipoContrib())
                && !Utils.isNullOrEmpty(rcRuc.getTbTipoContrib().getId())) {
            if (rcRuc.getTbTipoContrib().getId().getCclase() != null
                    && rcRuc.getTbTipoContrib().getId().getCclase().length() > 0) {
                hql += "and a.tbTipoContrib.id.cclase = '"
                        + rcRuc.getTbTipoContrib().getId().getCclase() + "'";
            }
        }

        if (rcRuc.getS1apeRasoc() != null
                && rcRuc.getS1apeRasoc().trim().length() > 0) {
            hql += "and a.s1apeRasoc like '"
                    + Utils.validateSpecialCharacters(rcRuc.getS1apeRasoc()
                    .trim().toUpperCase()) + "%'";
        }
        if (rcRuc.getS2apeAbrev() != null
                && rcRuc.getS2apeAbrev().trim().length() > 0) {
            hql += "and a.s2apeAbrev like '"
                    + Utils.validateSpecialCharacters(rcRuc.getS2apeAbrev()
                    .trim().toUpperCase()) + "%'";
        }
        if (rcRuc.getSnombres() != null
                && rcRuc.getSnombres().trim().length() > 0) {
            hql += "and a.snombres like '"
                    + Utils.validateSpecialCharacters(rcRuc.getSnombres()
                    .trim().toUpperCase()) + "%'";
        }
        if (rcRuc.getRcCaractTribs() != null
                && rcRuc.getRcCaractTribs().size() > 0) {
            hql += "and ct.id.ccaractTrib = 'NRC' ";
            for (Iterator it = rcRuc.getRcCaractTribs().iterator(); it
                    .hasNext();) {
                RcCaractTrib nrc = (RcCaractTrib) it.next();
                hql += "and ct.id.ncaractTrib = '"
                        + Utils.validateSpecialCharacters(nrc.getId()
                        .getNcaractTrib()) + "'";
            }
        }

        if (rcRuc.getRcNomComercs() != null
                && rcRuc.getRcNomComercs().size() > 0) {
            for (Iterator iter = rcRuc.getRcNomComercs().iterator(); iter
                    .hasNext();) {
                RcNomComerc nom = (RcNomComerc) iter.next();
                hql += "and upper(nc.id.snomComercial)  like '%"
                        + Utils.validateSpecialCharacters(
                        nom.getSnomComercial()).toUpperCase() + "%'";
            }

        }

        if (rcRuc.getRcDocIdentidads() != null
                && rcRuc.getRcDocIdentidads().size() > 0) {
            for (Iterator iter = rcRuc.getRcDocIdentidads().iterator(); iter
                    .hasNext();) {
                RcDocIdentidad docId = (RcDocIdentidad) iter.next();
                hql += "and di.ndocIdentidad  = '" + docId.getNdocIdentidad()
                        + "'";
            }

        }

        if (isValidSet(rcRuc.getRcDomicilios())) {
            for (RcDomicilio domicilio : rcRuc.getRcDomicilios()) {
                if (!Utils.isNullOrEmpty(domicilio.getScalle())) {
                    hql += "and dom.scalle like '"
                            + Utils.validateSpecialCharacters(domicilio
                            .getScalle()) + "%'";
                }
                if (!Utils.isNullOrEmpty(domicilio.getScomplemento())) {
                    hql += "and dom.scomplemento like '%"
                            + Utils.validateSpecialCharacters(domicilio
                            .getScomplemento()) + "%'";
                }
            }// for
        }

        hql += " order by a.tbPais.cpais, a.tbMunicipio.id.cdepartamento, a.tbMunicipio.id.cmunicipio,  a.s1apeRasoc desc";
//		return getHibernateTemplate().getSessionFactory().getCurrentSession()
//				.createQuery(hql).setMaxResults(MAX_RESULTS).list();
        return getSessionFactory().getCurrentSession()
                .createQuery(hql).setMaxResults(1000).list();

    }

    /**
     * retorna un dynabean con la informacion de la tarjeta de nit, utiliza el
     * mismo query que la impresion
     *
     * @param nit del contribuyente
     * @return DynaBean con info de Tarjeta de NIT
     */
    public DynaBean getInfoTarjetaNit(String nit) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("p_nit", nit);
        List<DynaBean> beans = executeSqlNamedQuery(_QUERY_TARJETA_NIT, params);
        if (beans != null && beans.size() > 0) {
            return beans.get(0);
        }
        return null;
    }

    /**
     * retorna un dynabean con la informacion de la tarjeta de nrc, utiliza el
     * mismo query q la impresion
     *
     * @param nit nit del contribuyente
     * @return DynaBean con la info de la tarjeta de NRC
     */
    public DynaBean getInfoTarjetaNrc(String nit) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("p_nit", nit);
        List<DynaBean> beans = executeSqlNamedQuery(_QUERY_TARJETA_NRC, params);

        if (beans != null && beans.size() > 0) {
            return beans.get(0);
        }
        return null;
    }

    public Integer getNextCorrelativo(String nitRaiz) {
        String hql = "select max(to_number(substr(nit,length(nit)-3, 3))) +1 from RcRuc where  nit like ?";
        Object[] params = new Object[]{nitRaiz.concat("%")};
        List lst = executeHql(hql, params);
        Object obj = lst.get(0);
        Integer correlativo = (Integer) obj;
        if (correlativo == null) {
            correlativo = 101;
        } else if (correlativo <= 100) {
            correlativo = 101;
        } else if (correlativo > 999) {
            throw new RuntimeException();
        }
        return correlativo;
    }

    public String getDireccion(String nit, String tipoDireccion)
            throws Exception {

        String sql = "{ ? = call pkg_ruc_utils.get_direccion(?,?) }";
        return (String) executeFunction(sql, new Object[]{"", nit,
                    tipoDireccion});
    }

    public String getCaracterTributario(String nit, String tipoCaracter)
            throws Exception {

        String sql = "{ ? = call pkg_ruc_utils.get_caracter_tributario(?,?) }";
        return (String) executeFunction(sql, new Object[]{"", nit,
                    tipoCaracter});
    }

    public String getFechaIngreso(String nit) throws Exception {

        String sql = "{ ? = call pkg_ruc_utils.f_get_fecha_ingreso(?) }";
        return (String) executeFunction(sql, new Object[]{"", nit});
    }

    public String getRepresentante(String nit) throws Exception {
        String sql = "{ ? = call PKG_RUC_UTILS.get_apoderado(?)}";
        return (String) executeFunction(sql, new Object[]{"", nit});
    }

    public Integer getDigitoChequeo(String nit, String tipo)
            throws SQLException {
        String sql = "{ ? = call pkg_ruc_utils.f_calcula_dv(?,?) }";
        Object params[] = new Object[]{new Integer(0), nit, tipo};
        return (Integer) executeFunction(sql, params);
    }

    public void termino(Object[] params) throws SQLException {
        // Parameters
        // result,nit,causa,tipoPersona,documento,folio,gestion,admintrib
        // ,unidadrecep,usuario
        String sql = "{ ? = call pkg_ruc_utils.termino(?,?,?,?,?,?,?,?,?) }";
        String result = (String) executeFunction(sql, params);
    }

    public List getRiskList(String snombres, String s1apeRasoc,
            String s2apeAbrev, String cclase) {
        String sql = "SELECT   * "
                + "FROM (SELECT rcruc0_.* "
                + "FROM ruc.rc_ruc rcruc0_ LEFT OUTER JOIN ruc.rc_caract_trib rccaracttr1_ "
                + "ON rcruc0_.nit = rccaracttr1_.nit "
                + "WHERE rcruc0_.nit = rcruc0_.nit "
                + "AND rcruc0_.c_clase = ? "
                + "AND (rcruc0_.s_1ape_rasoc LIKE ?) "
                + "AND (rcruc0_.s_2ape_abrev LIKE ?) "
                + "AND (rcruc0_.s_nombres LIKE ?) "
                + "UNION "
                + "SELECT rcruc0_.* "
                + "FROM ruc.rc_ruc rcruc0_ LEFT OUTER JOIN ruc.rc_caract_trib rccaracttr1_ "
                + "ON rcruc0_.nit = rccaracttr1_.nit "
                + "WHERE rcruc0_.nit = rcruc0_.nit "
                + "AND rcruc0_.c_clase = ? "
                + "AND (rcruc0_.s_1ape_rasoc LIKE ?) "
                + "AND (rcruc0_.s_nombres LIKE ?)) " + "WHERE ROWNUM <= ? "
                + "ORDER BY s_1ape_rasoc DESC";
        SQLQuery q = getSessionFactory().getCurrentSession().createSQLQuery(sql).addEntity(RcRuc.class);
        q.setString(0, cclase);
        q.setString(1, s1apeRasoc + "%");
        q.setString(2, s2apeAbrev + "%");
        q.setString(3, snombres + "%");
        q.setString(4, cclase);
        q.setString(5, s1apeRasoc + "%");
        q.setString(6, snombres + "%");
        q.setInteger(7, _RC_MAX_RESULTS);
        return q.list();
    }

    public String getNombre(String nit)
            throws Exception {
        String sql = "{ ? = call pkg_ruc_utils.get_nombre(?) }";
        return (String) executeFunction(sql, new Object[]{"", nit});
    }

    public boolean isNitInscrito(String nit, Date periodo) {
        String sql = "{ ? = call pkg_ruc_utils.is_inscrito_obligacion_num(?, ?, ?) }";
        Integer ret;
        try {
            ret = (Integer) executeFunction(sql, new Object[]{0, nit, new java.sql.Date(periodo.getTime()), "20"});
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return (ret.equals(1));
    }

    public RcRuc findByNit(String nit) {
        Session session = getSessionFactory().getCurrentSession();
        final Criteria crit = session.createCriteria(RcRuc.class);
        crit.setFetchMode("tbTipoContrib", FetchMode.JOIN);
        crit.setFetchMode("tbTipoContrib.tbClaseContrib", FetchMode.JOIN);
        crit.add(Property.forName("nit").eq(nit));
        RcRuc ruc = (RcRuc) crit.uniqueResult();
        if (ruc == null) {
            throw new ObjectRetrievalFailureException(clazz, nit);
        }
        return ruc;
    }

    public List<DynaBean> findAduanasImportaciones(String nit, String fini, String ffin) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("p_nit", nit);
        params.put("p_fini", fini);
        params.put("p_ffin", ffin);
        List lst = executeSqlNamedQuery("RcRuc.findAduanasImportaciones", params);
        return (List<DynaBean>) lst;
    }

    public List<DynaBean> findAduanasInternaciones(String nit, String fini, String ffin) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("p_nit", nit);
        params.put("p_fini", fini);
        params.put("p_ffin", ffin);
        List lst = executeSqlNamedQuery("RcRuc.findAduanasInternaciones", params);
        return (List<DynaBean>) lst;
    }

    public List<DynaBean> findAduanasExportaciones(String nit, String fini, String ffin) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("p_nit", nit);
        params.put("p_fini", fini);
        params.put("p_ffin", ffin);
        List lst = executeSqlNamedQuery("RcRuc.findAduanasExportaciones", params);
        return (List<DynaBean>) lst;
    }

    public List<DynaBean> findAduanasReexportaciones(String nit, String fini, String ffin) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("p_nit", nit);
        params.put("p_fini", fini);
        params.put("p_ffin", ffin);
        List lst = executeSqlNamedQuery("RcRuc.findAduanasReexportaciones", params);
        return (List<DynaBean>) lst;
    }

    public List<DynaBean> findCasoxSupervisor(String nitUsuario, String key,
            String tipo) {
        // TODO Auto-generated method stub
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("p_nit", key);
        params.put("p_rol", tipo);
        List lst = executeSqlNamedQuery("RcRuc.findCasoxSupervisor", params);
        return (List<DynaBean>) lst;

    }

    public List<DynaBean> findUnixTec(String nitUsuario) {
        // TODO Auto-generated method stub
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("p_tecnico", nitUsuario);
        List lst = executeSqlNamedQuery("RcRuc.findUnixTec", params);
        return (List<DynaBean>) lst;
    }

    public boolean isBienestar(String nit) {
        String sql = "{ ? = call SIIT.PKG_EDA_SUGERIDA_INTERNET.AGENTE_BIENESTAR(?) }";
        Integer ret;
        try {
            ret = (Integer) executeFunction(sql, new Object[]{0, nit});
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return (ret.equals(1));
    }

    public boolean isIpsfa(String nit) {
        String sql = "{ ? = call SIIT.PKG_EDA_SUGERIDA_INTERNET.AGENTE_IPSFA(?) }";
        Integer ret;
        try {
            ret = (Integer) executeFunction(sql, new Object[]{0, nit});
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return (ret.equals(1));
    }

    public RcRuc findRepresentanteDeImSolicitud(ImSolicitud imSolicitud) {
        RcRuc ret = null;
        Query q = getSessionFactory().getCurrentSession().createQuery("SELECT  rcRucByNitRepresentante FROM ImSolicitud s WHERE s.nsolicitud = :nsolicitud");
        q.setParameter("nsolicitud", imSolicitud.getNsolicitud());
        List<RcRuc> list = q.list();
        if (list != null && list.size() > 0) {
            ret = list.get(0);
        }
        return ret;
    }

    @Override
    public RcRuc findPropietarioDeImSolicitud(ImSolicitud imSolicitud) {
        RcRuc ret = null;
        Query q = getSessionFactory().getCurrentSession().createQuery("SELECT  rcRucByNitPropietario FROM ImSolicitud s WHERE s.nsolicitud = :nsolicitud");
        q.setParameter("nsolicitud", imSolicitud.getNsolicitud());
        List<RcRuc> list = q.list();
        if (list != null && list.size() > 0) {
            ret = list.get(0);
        }
        return ret;
    }
}// class